--    Author    : ChenErHao
--    Name      : DM.MID_T_MTH_CURRENT_M_AMONT.HQL
--    Functions : 利率-按当月发生业务计算
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-23  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.MID_T_MTH_CURRENT_M_AMONT PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
 SELECT
   BELONG_AREA_CODE, 
   SUM(DEP_AMOUNT) DEP_AMOUNT, 
   SUM(DEP_AMOUNT_AMT) DEP_AMOUNT_AMT, 
   SUM(LOAN_AMOUNT) LOAN_AMOUNT, 
   SUM(LOAN_AMOUNT_AMT) LOAN_AMOUNT_AMT, 
   SUM(DEP_AMOUNT_L) DEP_AMOUNT_L, 
   SUM(DEP_AMOUNT_AMT_L) DEP_AMOUNT_AMT_L, 
   SUM(LOAN_AMOUNT_L) LOAN_AMOUNT_L, 
   SUM(LOAN_AMOUNT_AMT_L) LOAN_AMOUNT_AMT_L,
   cast(sum(WARL) as double) WARL 
  from 
    (
      SELECT 
         BELONG_AREA_CODE, 
         SUM(DEP_AMOUNT) DEP_AMOUNT, 
         SUM(DEP_AMOUNT_AMT) DEP_AMOUNT_AMT, 
         SUM(LOAN_AMOUNT) LOAN_AMOUNT, 
         SUM(LOAN_AMOUNT_AMT) LOAN_AMOUNT_AMT, 
         SUM(DEP_AMOUNT_L) DEP_AMOUNT_L, 
         SUM(DEP_AMOUNT_AMT_L) DEP_AMOUNT_AMT_L, 
         SUM(LOAN_AMOUNT_L) LOAN_AMOUNT_L, 
         SUM(LOAN_AMOUNT_AMT_L) LOAN_AMOUNT_AMT_L 
       from 
         (
           SELECT    
                DATA_AREA_CODE BELONG_AREA_CODE, 
                SUM(DEP_AMOUNT) DEP_AMOUNT, ---存款发生额
                SUM(DEP_AMOUNT_AMT) DEP_AMOUNT_AMT,
                0 LOAN_AMOUNT, 
                0 LOAN_AMOUNT_AMT, 
                0 DEP_AMOUNT_L, 
                0 DEP_AMOUNT_AMT_L, 
                0 LOAN_AMOUNT_L, 
                0 LOAN_AMOUNT_AMT_L  ---存款发生额加权金额 
                FROM  DM.MID_T_MTH_DEP_RATE_D011013 
             WHERE DATA_MONTH='#V_DATA_MONTH#'
            group by DATA_AREA_CODE 
                        
           UNION ALL   
           
           select   BELONG_AREA_CODE,        
                 0 DEP_AMOUNT,0 DEP_AMOUNT_AMT,
                 sum(LOAN_AMOUNT) LOAN_AMOUNT,sum(LOAN_AMOUNT_AMT),
                 sum(DEP_AMOUNT_L) DEP_AMOUNT_L,sum(DEP_AMOUNT_AMT_L) DEP_AMOUNT_AMT_L,
                 sum(LOAN_AMOUNT_L) LOAN_AMOUNT_L,sum(LOAN_AMOUNT_AMT_L) LOAN_AMOUNT_AMT_L
            from (
                  select 
                      BELONG_AREA_CODE,
                      0 DEP_AMOUNT ,
                      0 DEP_AMOUNT_AMT,
                       LOAN_OCCURRENCE LOAN_AMOUNT,
                       LOAN_OCCURRENCE_LEVEL LOAN_AMOUNT_AMT,
                      0 DEP_AMOUNT_L,0 DEP_AMOUNT_AMT_L,0 LOAN_AMOUNT_L,0 LOAN_AMOUNT_AMT_L
                  from EDW.LOAF A
                  WHERE  DATA_DATE ='#V_DATA_DATE#' 
                  
                  UNION ALL
                  
                  select 
                      AREA_NO_ID BELONG_AREA_CODE,
                      0 DEP_AMOUNT ,
                      0 DEP_AMOUNT_AMT,
                      0 LOAN_AMOUNT,
                      0 LOAN_AMOUNT_AMT,
                       DEP_AMOUNT DEP_AMOUNT_L, 
                       DEP_AMOUNT_AMT DEP_AMOUNT_AMT_L, 
                       LOAN_AMOUNT LOAN_AMOUNT_L, 
                       LOAN_AMOUNT_AMT LOAN_AMOUNT_AMT_L 
                  from DM.T_MTH_CURRENT_M_AMONT
                  WHERE 
                      FISCAL_MTH_DIMNSN_ID ='#V_LAST_1M_END_MONTH#' and  AREA_NO_ID!=-1 
              )t
            GROUP BY  
                 BELONG_AREA_CODE 
         ) TT
        GROUP BY 
            BELONG_AREA_CODE
    ) A
     LEFT JOIN 
        (
          SELECT 
           AREA_NO_ID
          ,SUM(WARL) WARL
           FROM 
            (
              select 
                AREA_NO_ID, 
                CASE WHEN SUM(LOAN_AMOUNT) IS NULL THEN 0
                    WHEN SUM(LOAN_AMOUNT) ='' THEN 0
                    ELSE round(SUM(LOAN_AMOUNT_AMT)*1.000 /SUM(LOAN_AMOUNT),4) 
                 END -
                CASE WHEN SUM(DEP_AMOUNT) IS NULL THEN 0
                    WHEN SUM(DEP_AMOUNT) ='' THEN 0
                    ELSE round(SUM(DEP_AMOUNT_AMT)*1.0000 /SUM(DEP_AMOUNT),4) 
                END AS WARL                                                
               from DM.T_MTH_CURRENT_M_AMONT
              WHERE FISCAL_MTH_DIMNSN_ID BETWEEN '#V_LAST_13M_END_MONTH#' AND '#V_LAST_1M_END_MONTH' and AREA_NO_ID!=-1 
              GROUP BY FISCAL_MTH_DIMNSN_ID,AREA_NO_ID 
            )TT 
          GROUP BY AREA_NO_ID
        ) B 
          ON A.BELONG_AREA_CODE= B.AREA_NO_ID where A.BELONG_AREA_CODE!=-1 
     GROUP BY 
          BELONG_AREA_CODE
;